package com.wut.easyexcel.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.wut.core.common.enums.BaseEnum;
import com.wut.core.config.exception.GlobalException;
import com.wut.easyexcel.annotation.ExcelDropDown;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
import lombok.AccessLevel;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.http.HttpHeaders;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;

@Slf4j
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelUtil {

    /**
     * 导入Excel(单sheet页)
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return readExcel(file.getInputStream(), pojoClass, new ExcelListener<>());
    }

    /**
     * 导入Excel(单sheet页)
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> pojoClass, ExcelListener<T> excelListener) throws IOException {
        return readExcel(file.getInputStream(), pojoClass, excelListener);
    }

    /**
     * 导入Excel(单sheet页)
     */
    public static <T> List<T> readExcel(InputStream inputStream, Class<T> pojoClass, ExcelListener<T> excelListener) {
        return readExcel(inputStream, 0, pojoClass, excelListener);
    }

    /**
     * 读取Excel文件返回数据集合，不包含表头，读取第x个sheet数据，不设置sheet就读取全部
     */
    public static <T> List<T> readExcel(InputStream inputStream, Integer sheetNo, Class<T> pojoClass, ExcelListener<T> excelListener) {
        if (ObjectUtil.isNull(inputStream) || ObjectUtil.isNull(pojoClass) || ObjectUtil.isNull(excelListener)) {
            return Collections.emptyList();
        }
        ExcelReaderBuilder read = EasyExcelFactory.read(inputStream, pojoClass, excelListener);
        if (ObjectUtil.isNotNull(sheetNo)) {
            read.sheet(sheetNo).doRead();
        } else {
            ExcelReader excelReader = read.build();
            excelReader.readAll();
            excelReader.finish();
        }
        return excelListener.getDataList();
    }

    /**
     * 导出Excel(单sheet页)(排除失败原因字段)
     *
     * @param dataList  数据
     * @param pojoClass 数据类型
     * @param fileName  导出Excel文件名
     * @param sheetName 导出Excel Sheet名
     * @param response  response
     */
    public static <T> void writeExcelWithoutFailedMessage(List<T> dataList, Class<T> pojoClass, String fileName, String sheetName, HttpServletResponse response) {
        Set<String> excludeColumnFiledNameList = new HashSet<>();
        excludeColumnFiledNameList.add("failedMessage");
        writeExcel(dataList, pojoClass, fileName, sheetName, response, excludeColumnFiledNameList);
    }

    /**
     * 导出Excel(单sheet页)
     *
     * @param dataList  数据
     * @param pojoClass 数据类型
     * @param fileName  导出Excel文件名
     * @param sheetName 导出Excel Sheet名
     * @param response  response
     */
    public static <T> void writeExcel(List<T> dataList, Class<T> pojoClass, String fileName, String sheetName, HttpServletResponse response) {
        if (ObjectUtil.isNull(response) || ObjectUtil.isNull(pojoClass)) {
            return;
        }

        try {
            EasyExcelFactory.write(buildResponse(response, fileName), pojoClass)
                .registerWriteHandler(buildCellStyle())
                .registerWriteHandler(new CellFormatWriteHandler())
                .registerWriteHandler(new FixedHeadWriteHandler())
                .registerWriteHandler(new CustomizeWidthHandler())
                .registerWriteHandler(new DropDownWriteHandler(pojoClass))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .doWrite(dataList);
        } catch (Exception e) {
            throw new GlobalException(e.getMessage());
        }
        log.info("导出Excel数据条数：{}条", dataList.size());
    }

    /**
     * 导出Excel(单sheet页)(排除字段)
     *
     * @param dataList                   数据
     * @param pojoClass                  数据类型
     * @param fileName                   导出Excel文件名
     * @param sheetName                  导出Excel Sheet名
     * @param response                   response
     * @param excludeColumnFiledNameList 导出排除字段名
     */
    public static <T> void writeExcel(List<T> dataList, Class<T> pojoClass, String fileName, String sheetName, HttpServletResponse response, Set<String> excludeColumnFiledNameList) {
        if (ObjectUtil.isNull(response) || ObjectUtil.isNull(pojoClass)) {
            return;
        }

        try {
            EasyExcelFactory.write(buildResponse(response, fileName), pojoClass)
                .excludeColumnFieldNames(excludeColumnFiledNameList)
                .registerWriteHandler(buildCellStyle())
                .registerWriteHandler(new CellFormatWriteHandler())
                .registerWriteHandler(new FixedHeadWriteHandler())
                .registerWriteHandler(new CustomizeWidthHandler())
                .registerWriteHandler(new DropDownWriteHandler(pojoClass))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .doWrite(dataList);
        } catch (Exception e) {
            throw new GlobalException(e.getMessage());
        }
        log.info("导出Excel数据条数：{}条", dataList.size());
    }

    /**
     * 导出Excel为MultipartFile
     *
     * @param dataList  数据
     * @param pojoClass 数据类型
     * @param fileName  导出Excel文件名
     * @param sheetName 导出Excel Sheet名
     */
    public static <T> MultipartFile writeExcel(List<T> dataList, Class<T> pojoClass, String fileName, String sheetName) {
        try {
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            EasyExcelFactory.write(byteArrayOutputStream, pojoClass)
                .registerWriteHandler(buildCellStyle())
                .registerWriteHandler(new CellFormatWriteHandler())
                .registerWriteHandler(new FixedHeadWriteHandler())
                .registerWriteHandler(new CustomizeWidthHandler())
                .registerWriteHandler(new DropDownWriteHandler(pojoClass))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .doWrite(dataList);
            log.info("导出Excel数据条数：{}条", dataList.size());
            MultipartFile file = new MockMultipartFile("file", fileName + ExcelTypeEnum.XLSX.getValue(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                byteArrayOutputStream.toByteArray());
            return file;
        } catch (Exception e) {
            throw new GlobalException(e.getMessage());
        }
    }

    /**
     * 导出Excel(多sheet页)
     *
     * @param dataMap   数据(key:sheetName,value:数据)
     * @param pojoClass 数据类型
     * @param fileName  导出Excel文件名
     * @param response  response
     */
    public static <T> void writeExcel(Map<String, List<T>> dataMap, Class<T> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        if (ObjectUtil.isNull(response) || ObjectUtil.isNull(pojoClass)) {
            return;
        }

        try (ExcelWriter excelWriter = EasyExcelFactory.write(buildResponse(response, fileName), pojoClass).build()) {
            for (String sheetName : dataMap.keySet()) {
                WriteSheet writeSheet = new WriteSheet();
                writeSheet.setSheetName(sheetName);
                excelWriter.write(dataMap.get(sheetName), writeSheet);
            }
        }
        log.info("导出Excel的sheet页数量：{}", dataMap.size());
    }

    /**
     * 导出Excel(单sheet页)
     *
     * @param dataList     数据
     * @param pojoClass    数据类型
     * @param sheetName    导出Excel Sheet名
     * @param inputStream  模板流
     * @param outputStream 导出文件流
     */
    public static <T> void writeExcel(List<T> dataList, Class<T> pojoClass, String sheetName, InputStream inputStream, OutputStream outputStream) {
        if (ObjectUtil.isNull(outputStream) || ObjectUtil.isNull(pojoClass)) {
            return;
        }
        EasyExcelFactory.write(outputStream, pojoClass).excelType(ExcelTypeEnum.XLSX).withTemplate(inputStream).sheet(sheetName).doFill(dataList);
        log.info("导出Excel数据条数：{}条", dataList.size());
    }

    private static OutputStream buildResponse(HttpServletResponse response, String fileName) throws IOException {
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setStatus(HttpServletResponse.SC_OK);
        response.setContentType("application/x-msdownload");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", StandardCharsets.UTF_8.name()));
        return response.getOutputStream();
    }

    /**
     * 读取文件解析监听类
     */
    public static class ExcelListener<T> extends AnalysisEventListener<T> {

        /**
         * 读取后的Excel数据
         */
        @Getter
        public List<T> dataList = new ArrayList<>();

        @Override
        public void invoke(T t, AnalysisContext analysisContext) {
            dataList.add(t);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            log.info("导入Excel数据条数：{}条", dataList.size());
        }
    }

    /**
     * 设置自适应列宽配置类
     */
    public static class CustomizeWidthHandler extends AbstractColumnWidthStyleStrategy {

        private static final int MAX_COLUMN_WIDTH = 255;
        // 因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
        private static final int COLUMN_WIDTH = 20;
        private final Map<Integer, Map<Integer, Integer>> cache = new HashMap<>(8);

        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            boolean needSetWidth = isHead || !CollUtil.isEmpty(cellDataList);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = cache.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>(16));

                Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > MAX_COLUMN_WIDTH) {
                        columnWidth = MAX_COLUMN_WIDTH;
                    } else {
                        if (columnWidth < COLUMN_WIDTH) {
                            columnWidth = columnWidth * 2;
                        }
                    }

                    Integer maxColumnWidth = (Integer) ((Map<?, ?>) maxColumnWidthMap).get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }
                }
            }
        }

        private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                WriteCellData<?> cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                } else {
                    switch (type) {
                        case STRING:
                            return cellData.getStringValue().getBytes().length;
                        case BOOLEAN:
                            return cellData.getBooleanValue().toString().getBytes().length;
                        case NUMBER:
                            return cellData.getNumberValue().toString().getBytes().length;
                        default:
                            return -1;
                    }
                }
            }
        }
    }

    /**
     * 设置单元格格式
     */
    public static class CellFormatWriteHandler extends AbstractCellStyleStrategy {

        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
            Workbook workbook = context.getCell().getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();

            // 设置单元格格式
            setRowFormatStyle(workbook, cellStyle, context.getCell());
        }

        private void setRowFormatStyle(Workbook workbook, CellStyle cellStyle, Cell cell) {
            DataFormat format = workbook.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));

            if (StrUtil.equals(cell.getCellType().name(), CellType.STRING.name())) {
                cell.getSheet().setDefaultColumnStyle(cell.getColumnIndex(), cellStyle);
            }
        }
    }

    /**
     * 固定表头/筛选
     */
    public static class FixedHeadWriteHandler implements SheetWriteHandler {

        // 固定表头
        public int colSplit = 0, rowSplit = 1, leftmostColumn = 0, topRow = 1;
        // 筛选
        public String autoFilterRange = "1:1";

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            // 固定表头
            sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
            // 筛选
            //sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange));
        }
    }

    /**
     * 下拉框
     */
    public static class DropDownWriteHandler implements CellWriteHandler {

        private final Map<String, List<String>> dropDownMap;

        public DropDownWriteHandler(Class<?> pojoClass) {
            dropDownMap = buildDropDownMap(pojoClass);
        }

        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            Sheet sheet = writeSheetHolder.getCachedSheet();
            DataValidationHelper helper = sheet.getDataValidationHelper();

            // 区间设置
            if (dropDownMap.keySet().contains(head.getFieldName())) {
                // 设置下拉框：首行,末行,首列,末列
                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 1, cell.getRowIndex(), cell.getColumnIndex());
                // 设置下拉框值
                DataValidationConstraint constraint = helper.createExplicitListConstraint(dropDownMap.get(head.getFieldName()).toArray(new String[0]));
                DataValidation validation = helper.createValidation(constraint, cellRangeAddressList);
                // 阻止输入非下拉选项的值
                //validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                //validation.setShowErrorBox(true);
                //validation.setSuppressDropDownArrow(true);
                //validation.createErrorBox("提示", "请输入下拉选项中的内容");
                sheet.addValidationData(validation);
            }
        }

        private Map<String, List<String>> buildDropDownMap(Class<?> pojoClass) {
            Map<String, List<String>> dropDownMap = new HashMap<>(8);
            Field[] fields = ReflectUtil.getFields(pojoClass);
            Arrays.stream(fields)
                .filter(field -> ObjectUtil.isNotNull(field.getAnnotation(ExcelDropDown.class)))
                .forEach(field -> {
                    List<String> dropDownList = new ArrayList<>();
                    ExcelDropDown dropDown = field.getAnnotation(ExcelDropDown.class);
                    if (BaseEnum.class.isAssignableFrom(dropDown.type())) {
                        try {
                            Method method = dropDown.type().getMethod("values");
                            BaseEnum[] baseEnums = (BaseEnum[]) method.invoke(null, null);
                            for (BaseEnum baseEnum : baseEnums) {
                                dropDownList.add(baseEnum.getValue());
                            }
                        } catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException e) {
                            throw new GlobalException(e.getMessage());
                        }
                    } else if (ObjectUtil.isNotNull(dropDown.value())) {
                        String[] value = dropDown.value();
                        dropDownList.addAll(Arrays.asList(value));
                    }
                    if (CollUtil.isNotEmpty(dropDownList)) {
                        dropDownMap.put(field.getName(), dropDownList);
                    }
                });
            return dropDownMap;
        }
    }

    /**
     * 设置单元格样式
     */
    private static HorizontalCellStyleStrategy buildCellStyle() {
        // 表头样式
        WriteCellStyle head = new WriteCellStyle();
        // 设置表头居中对齐
        head.setHorizontalAlignment(HorizontalAlignment.CENTER);
        head.setVerticalAlignment(VerticalAlignment.CENTER);
        // 颜色
        head.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 字体
        WriteFont front = new WriteFont();
        front.setFontHeightInPoints((short) 14);
        head.setWriteFont(front);
        // 自动换行
        head.setWrapped(true);

        // 内容样式
        WriteCellStyle content = new WriteCellStyle();
        // 设置内容靠中对齐
        content.setHorizontalAlignment(HorizontalAlignment.CENTER);
        content.setVerticalAlignment(VerticalAlignment.CENTER);
        // 自动换行
        content.setWrapped(true);

        return new HorizontalCellStyleStrategy(head, content);
    }
}